import pandas as pd
import geopandas as gpd
# from jupyterthemes import jtplot
import matplotlib.pyplot as plt
import plotly.express as px
import plotly.io as pio
import basedosdados as bd
import sqlite3 as sqlite
pio.renderers.default='notebook'
dbpath = 'cities.db'
# Create connection and load spatialite extension
con = sqlite.connect(dbpath)
con.enable_load_extension(True)
con.execute('SELECT load_extension("mod_spatialite")')
# SQL must wrap the geometry in hex(st_asbinary(...))
sql = "SELECT id, sigla_uf, name, Hex(ST_AsBinary(geometry)) as geom FROM cities;"
df = gpd.GeoDataFrame.from_postgis(sql, con, geom_col="geom")
life_exp = bd.read_sql(
'''
SELECT id_municipio AS id, expectativa_vida
FROM basedosdados.mundo_onu_adh.municipio
WHERE ano = 2010
''',
billing_project_id='adameplayground')
cities_health = pd.merge(df, life_exp, how='inner', on='id')
rj_health = cities_health[cities_health['sigla_uf'] == 'RJ']
rj_health
rj_health.plot(column ='expectativa_vida',figsize= (10,10))
#rj_health.to_csv('rj_health.gzip', compression = 'gzip')
#gdf[gdf['sigla_uf']== 'RJ'].plot(figsize= (10,10),cmap='tab20b')
rj_health['id'] = rj_health['id'].astype(int)
rj_health
fig = px.choropleth(rj_health,
geojson = rj_health["geom"],
locations = rj_health.index,
color = "expectativa_vida",
hover_name = "name",
hover_data = ["expectativa_vida"],#, "death_rate"],
labels = {
"expectativa_vida": "Life Expectancy"
},
color_continuous_scale = "sunsetdark",
range_color=(71, 77),
#animation_frame = 'date',
)
fig.update_geos(
projection=dict(
scale=55
),
center = dict(
lat=-22.208333,
lon=-42.896388
)
)
fig.show()
covid = pd.read_csv('caso_full.csv.gz', compression='gzip')
filt = (covid['state'] == 'RJ') & (covid['place_type'] == 'city') & (covid['is_last'])
uniq = covid[filt]['city'].isin(rj_health['name'])
rj_covid = covid[filt][uniq]
rj_covid['id'] = rj_covid['city_ibge_code'].astype(int)
rj_covid = rj_covid.copy()[['id', 'estimated_population', 'last_available_confirmed_per_100k_inhabitants', 'last_available_death_rate']]
rj_covid
health_covid_rj = pd.merge(rj_health, rj_covid, how='inner', on='id')
health_covid_rj
#health_covid_rj.to_csv('health_covid_rj.gzip', compression = 'gzip')
fig = px.choropleth(health_covid_rj,
geojson = health_covid_rj['geom'],
locations = health_covid_rj.index,
color = "last_available_death_rate",
hover_name = "name",
hover_data = ["expectativa_vida", "last_available_death_rate"],
labels = {
"expectativa_vida": "Life Expectancy",
"last_available_death_rate": "Death Rate"
},
color_continuous_scale = "sunsetdark",
#range_color=(71, 77),
#animation_frame = 'date',
)
fig.update_geos(
projection=dict(
scale=55
),
center = dict(
lat=-22.208333,
lon=-42.896388
)
)
fig.show()
fig = px.scatter(health_covid_rj, x="last_available_death_rate", y="expectativa_vida", hover_data=['name'], trendline="ols")
fig.show()
!jupyter nbconvert health_analysis.ipynb --to html --template classic